package com.css.fxfzaqbz.modules.callJcData.repository.impl;


import cn.hutool.core.util.PageUtil;
import com.css.fxfzaqbz.base.response.RestResponse;
import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.callJcData.entity.Gasholder;
import com.css.fxfzaqbz.modules.callJcData.entity.TableFieldDesc;
import com.css.fxfzaqbz.modules.callJcData.param.DataVersionPageParam;
import com.css.fxfzaqbz.modules.callJcData.param.SysDataVersionPageParam;
import com.css.fxfzaqbz.modules.callJcData.vo.SitePhotoVO;
import com.css.fxfzaqbz.modules.data.entity.Site;
import com.css.fxfzaqbz.modules.data.queryParams.SiteQueryParams;
import com.css.fxfzaqbz.modules.callJcData.repository.SiteRepository;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class SiteRepositoryImpl implements SiteRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Site updateSite(Site site) {

        /*String sql = "update sys_site set ss_zp = :ssZp ,ss_sgt = :ssSgt, ss_xfjhpsj = :ssXfjhpsj"+
                " where ss_id = '"+site.getSsId()+"'" ;*/
        String sql = "update sys_site set ss_province = :ssProvince ,ss_city = :ssCity ,ss_counties = :ssCounties ," +
                "ss_longitude = :ssLongitude ,ss_latitude = :ssLatitude ," +
                "ss_number = :ssNumber ,ss_jzwmc = :ssJzwmc ," +
                "ss_jglx = :ssJglx ,ss_jznd = :ssJznd ," +
                "ss_cs = :ssCs ,ss_cg = :ssCg ," +
                "ss_yt = :ssYt ,ss_plmgzcd = :ssPlmgzcd ," +
                "ss_sfbz = :ssSfbz ,ss_wglb = :ssWglb ," +
                "ss_jzgztd = :ssJzgztd ,ss_dwmjzj = :ssDwmjzj ," +
                "ss_bz = :ssBz ,update_user = :updateUser ,update_time = :updateTime ," +
                "ss_zp = :ssZp ,ss_sgt = :ssSgt, ss_xfjhpsj = :ssXfjhpsj, ss_confirm = :ssConfirm, " +
                "geom = :geom"+
                " where ss_id = '"+site.getSsId()+"'" ;

        SqlParameterSource source = new BeanPropertySqlParameterSource(site);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql,source,keyHolder);
        return site;
    }

    @Override
    @Transactional
    public Site saveSite(Site site) {
        String sql = "insert into sys_site (" +
                "ss_id,ss_number," +
                "ss_jglx,ss_jzwmc," +
                "ss_jznd,ss_cs,ss_cg," +
                "ss_yt,ss_plmgzcd,ss_sfbz," +
                "ss_wglb," +
                "ss_jzgztd,ss_dwmjzj," +
                "ss_bz,del_flag," +
                "create_time,ss_longitude,ss_latitude,create_user," +
                "ss_province,ss_city,ss_counties,source_flag,data_version,geom)" +
                " values(" +
                ":ssId,:ssNumber," +
                ":ssJglx,:ssJzwmc," +
                ":ssJznd,:ssCs," +
                ":ssCg,:ssYt," +
                ":ssPlmgzcd,:ssSfbz," +
                ":ssWglb,"+
                ":ssJzgztd,:ssDwmjzj," +
                ":ssBz,:delFlag," +
                ":createTime,:ssLongitude,:ssLatitude,:createUser," +
                ":ssProvince,:ssCity,:ssCounties,:sourceFlag,:dataVersion,ST_GeometryFromText(:geom,4490))";

        SqlParameterSource source = new BeanPropertySqlParameterSource(site);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return site;
    }

    @Override
    public Map<String,Object> getAllSiteParams(SiteQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  *,ST_AsText(geom) geom from sys_site  ");

        StringBuilder orderSql = new StringBuilder(" order by ss_number");

        StringBuilder whereSql = new StringBuilder(" where del_flag = '" + YNEnum.N.toString() + "'");

//        if (!PlatformObjectUtils.isEmpty(queryParams.getElevationBand()) ){
//            whereSql.append(" and e.elevation_band like '%" + queryParams.getElevationBand() + "%'");
//        }
//
//        if (!PlatformObjectUtils.isEmpty(queryParams.getElevationNodatavalue()) ){
//           whereSql.append(" and e.elevation_nodatavalue like '%" + queryParams.getElevationNodatavalue() + "%'");
//        }
        //查询条件
        if (!PlatformObjectUtils.isEmpty(queryParams.getSsProvince()) ){
            whereSql.append(" and ss_province like '%" + queryParams.getSsProvince() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSsCity()) ){
            whereSql.append(" and ss_city like '%" + queryParams.getSsCity() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSsCounties()) ){
            whereSql.append(" and ss_counties like '%" + queryParams.getSsCounties() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSsNumber()) ){
            whereSql.append(" and ss_number like '%" + queryParams.getSsNumber() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSsJzwmc()) ){
            whereSql.append(" and ss_jzwmc like '%" + queryParams.getSsJzwmc() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSsFileuuid()) ){
            whereSql.append(" and ss_fileuuid like '%" + queryParams.getSsFileuuid() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSsConfirm()) ){
            whereSql.append(" and ss_confirm ='" + queryParams.getSsConfirm() + "'");
        }
        //int firstIndex=(queryParams.getCurPage()-1) * queryParams.getPageSize();
        //int lastIndex=queryParams.getCurPage() * queryParams.getPageSize();
        sql.append(whereSql).append(orderSql).toString();
        //StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Site.class)).size();
        //List<Site> sites = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(Site.class));
        //不分页，全部查询
        List<Site> sites = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Site.class));
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",sites);
        return map;
    }

    @Override
    public Site getSiteById(String id) {
        String sql = "select *,ST_AsText(geom) geom from sys_site where ss_id = '"+id+"'  and del_flag = '"+YNEnum.N.toString()+"'";
        Site site = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Site.class));
        return site;

    }

    @Override
    public List<Site> getSiteByName(String name) {
        String sql = "select *,ST_AsText(geom) geom from sys_site where ss_jzwmc = '"+name+"'  and del_flag = '"+YNEnum.N+"'";
        List<Site> sites = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Site.class));
        return sites;
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteSitesById(List<String> ids, Timestamp timestamp,String userId) {
        String sql = "update sys_site set del_flag = '" + YNEnum.Y + "' , update_time = '"+timestamp+"' , update_user = '"+userId+"'" + " where ss_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<Site> getAll() {
        return jdbcTemplate.query("select *,ST_AsText(geom) geom from sys_site where del_flag = '"+YNEnum.N+"'",
                new BeanPropertyRowMapper<>(Site.class));
    }

    @Override
    public void importExcel(List<Site> sites) {
        jdbcTemplate.batchUpdate("insert into sys_site (" +
                "ss_id,ss_number," +
                "ss_jglx,ss_jzwmc," +
                "ss_jznd,ss_cs," +
                "ss_cg,ss_yt," +
                "ss_plmgzcd,ss_sfbz," +
                "ss_wglb,"+
                "ss_jzgztd,ss_dwmjzj," +
                "ss_bz,del_flag," +
                "create_user,create_time," +
                "ss_longitude,ss_latitude," +
                "ss_province,ss_city,ss_counties)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return sites.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, sites.get(i).getSsId());
                ps.setString(2,sites.get(i).getSsNumber());
                ps.setString(3,sites.get(i).getSsJglx());
                ps.setString(4,sites.get(i).getSsJzwmc());
                ps.setString(5,sites.get(i).getSsJznd());
                ps.setDouble(6,sites.get(i).getSsCs());
                ps.setDouble(7,sites.get(i).getSsCg());
                ps.setString(8,sites.get(i).getSsYt());
                ps.setString(9,sites.get(i).getSsPlmgzcd());
                ps.setString(10,sites.get(i).getSsSfbz());
                ps.setString(11,sites.get(i).getSsWglb());
                ps.setString(12,sites.get(i).getSsJzgztd());
                ps.setDouble(13,sites.get(i).getSsDwmjzj());
                ps.setString(14,sites.get(i).getSsBz());
                ps.setString(15,sites.get(i).getDelFlag());
                ps.setString(16,sites.get(i).getCreateUser());
                java.sql.Date createtime = new java.sql.Date(sites.get(i).getCreateTime().getTime()) ;
                ps.setDate(17,createtime);
                ps.setDouble(18,sites.get(i).getSsLongitude());
                ps.setDouble(19,sites.get(i).getSsLatitude());
                ps.setString(20,sites.get(i).getSsProvince());
                ps.setString(21,sites.get(i).getSsCity());
                ps.setString(22,sites.get(i).getSsCounties());
            }
        });
    }

    @Override
    public String getCount() {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from sys_site where 1=1  ");
        try {
            Integer a = jdbcTemplate.queryForObject(sqlBuilder.toString(), Integer.class);
            return String.valueOf(a);
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<Site> getSiteDataView(SysDataVersionPageParam sysDataVersionPageParam) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT *,ST_AsText(geom) geom  from  sys_site where 1=1 ");
        if (!sysDataVersionPageParam.getVersionCode().equals("")){
            sqlBuilder.append("and source_flag = '").append(sysDataVersionPageParam.getDataRangeCountry()).append("' ");
        }
        if (!sysDataVersionPageParam.getVersionCode().equals("")){
            sqlBuilder.append("and data_version like '%"+ sysDataVersionPageParam.getVersionCode()+ "%' ");
        }
        if (!sysDataVersionPageParam.getDataRangeProvince().equals("")){
            sqlBuilder.append("and ss_province = '").append(sysDataVersionPageParam.getDataRangeProvince()).append("' ");
        }
        if (!sysDataVersionPageParam.getDataRangeCity().equals("")){
            sqlBuilder.append("and ss_city = '").append(sysDataVersionPageParam.getDataRangeCity()).append("' ");
        }
        if (!sysDataVersionPageParam.getDataRangeArea().equals("")){
            sqlBuilder.append("and ss_counties = '").append(sysDataVersionPageParam.getDataRangeArea()).append("' ");
        }
        List<Site> sites = jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(Site.class));
        return sites;

    }

    @Override
    public void byShpBatchSave(List<Site> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "insert into sys_site (" +
                "ss_id,ss_number," +
                "ss_jglx,ss_jzwmc," +
                "ss_jznd,ss_cs,ss_cg," +
                "ss_yt,ss_plmgzcd,ss_sfbz," +
                "ss_wglb," +
                "ss_jzgztd,ss_dwmjzj," +
                "ss_bz,del_flag," +
                "create_time,ss_longitude,ss_latitude,create_user," +
                "ss_province,ss_city,ss_counties,source_flag,data_version,geom,if_submit,zd_num)" +
                " values(" +
                ":ssId,:ssNumber," +
                ":ssJglx,:ssJzwmc," +
                ":ssJznd,:ssCs," +
                ":ssCg,:ssYt," +
                ":ssPlmgzcd,:ssSfbz," +
                ":ssWglb,"+
                ":ssJzgztd,:ssDwmjzj," +
                ":ssBz,:delFlag," +
                ":createTime,:ssLongitude,:ssLatitude,:createUser," +
                ":ssProvince,:ssCity,:ssCounties,:sourceFlag,:dataVersion,ST_GeometryFromText(:geom,4490),:ifSubmit,:zdNum)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void deleteByVersion(String versionCode) {
        String sql = "update sys_site  set del_flag = '1' where data_version='" + versionCode + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void updateByLsVersion(String tempVersionCode, String versionCode,Integer sf) {
        String sql = "update sys_site  set del_flag = '0' ,data_version= '"+versionCode+"' ,source_flag='"+sf+"' where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<Site> getYwData(String versionCode) {
        StringBuilder stringBuilder=new StringBuilder("select *,ST_AsText(geom) geom from  sys_site where 1=1 and data_version='"+versionCode+"' and del_flag='"+YNEnum.N.toString()+"' and if_submit ='"+YNEnum.Y.toString()+"' ");
        List<Site> sites = null;
        try {
            sites = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(Site.class));
            return sites;
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public void uploadSitePhotos(SitePhotoVO sitePhotoVO) {
        StringBuilder sb=new StringBuilder("update sys_site  set ");
        if (sitePhotoVO.getPhotoType().equals("1")){
            sb.append(" ss_zp='"+sitePhotoVO.getPhotoId()+"' ");
        }
        if (sitePhotoVO.getPhotoType().equals("2")){
            sb.append(" ss_sgt='"+sitePhotoVO.getPhotoId()+"' ");
        }
        if (sitePhotoVO.getPhotoType().equals("3")){
            sb.append(" ss_xfjhpsj='"+sitePhotoVO.getPhotoId()+"' ");
        }
        sb.append(" where ss_id='" + sitePhotoVO.getSsId() + "'");
        jdbcTemplate.update(sb.toString());
    }

    @Override
    public List<TableFieldDesc> getByDictItemId(String dictItemId) {
        String sql = "select * from " + "table_field_desc" + " where dict_item_id = '" + dictItemId + "' order by sort asc";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TableFieldDesc.class));

    }

    @Override
    public void submitYwData(List<String> ids) {
        String sql = "update sys_site set if_submit = '" + YNEnum.Y.toString() + "'" +
                " where ss_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public void updateLs(String versionCode, String tempVersionCode) {
        String sql = "update sys_site  set del_flag = '0' ,data_version= '"+versionCode+"'  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public RestResponse getFyYwData(DataVersionPageParam dataVersionPageParam, String role) {
      StringBuilder sql=new StringBuilder("select *,ST_AsText(geom) geom from  sys_site  where del_flag = '0' and data_version = '" + dataVersionPageParam.getVersionCode() + "'");
        if (!"0".equals(role)&&!"1".equals(role)){
            sql.append(" and  province = '" + role + "'  or source_flag = '2' " );
        }
        String limitSql = dataVersionPageParam.getPage() != null && dataVersionPageParam.getRows() != null ? " limit " + dataVersionPageParam.getRows() + " offset " + dataVersionPageParam.getPage() : "";
        List<Site> query = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(Site.class));

        StringBuilder sql1 = new StringBuilder("select count(*) from sys_site where del_flag = '0' and data_version = '" + dataVersionPageParam.getVersionCode() + "'");
        if (!"0".equals(role)&&!"1".equals(role)){
            sql1.append(" and  province = '" + role + "'  or source_flag = '2' " );
        }
        Long aLong = jdbcTemplate.queryForObject(sql1.toString(), Long.class);
        return RestResponse.succeed(new HashMap<String, Object>() {{
            put("data", query);
            put("total", aLong);
            put("page", dataVersionPageParam.getPage());
            put("rows", dataVersionPageParam.getRows());
            put("totalPage", PageUtil.totalPage(aLong, dataVersionPageParam.getRows()));
        }});
    }

    @Override
    public List<Site> getSiteNumByVersion(String versionCode) {
        String sql = "select *, ST_AsText(geom) geom   from  sys_site  where data_version='" + versionCode + "' and del_flag = '0' ";
        List<Site> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Site.class));
        return query;
    }

}